package cn.sfa.service.routine;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.DateUtil;
import cn.sfa.entity.routine.SaleAgentbroker;
import cn.sfa.entity.routine.SaleCheckOnItem;
import cn.sfa.entity.routine.SaleCheckOnMain;
import cn.sfa.entity.routine.SaleCheckWorkOn;
import cn.sfa.entity.routine.SaleStatisticsAttendance;
import cn.sfa.entity.team.TeamAgentInfoEntity;

/**
 *@author 周友平
 *@version 创建时间：2012-2-27下午02:14:59 类说明 考勤管理
 */
@Service
public class AttendanceService {

	Logger log = LoggerFactory.getLogger(AttendanceService.class);

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	/*
	 * 获取被考勤ID与姓名
	 */
	public List<SaleAgentbroker> getCheckUser(String userID, String agentevel) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select agentid,agentcode,agentname,agentLevel from table(pkg_sfa_AttendanceService.getCheckUser(:USERID,:AGENTEVELCODE))";
		paramMap.put("AGENTEVELCODE", CommonUtil.jugeAndFixNull(agentevel));
		paramMap.put("USERID", CommonUtil.jugeAndFixNull(userID));
		
		List<SaleAgentbroker> affirm = new ArrayList<SaleAgentbroker>();
		affirm = jdbcTemplate.query(sqlStr, paramMap,
				new BeanPropertyRowMapper<SaleAgentbroker>(
						SaleAgentbroker.class));

		return affirm;
	}

	/*
	 * 插入主表
	 */
	public boolean insertCheckOnMain(SaleCheckOnMain mode) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("insert into SALECHECKONMAIN");
		sqlStr
				.append("(SALECHECKONMAINID,CREATEUSERID,CREATEDATE,UPDATEUSERID,UPDATEDATE,STARTDATETIME,");
		sqlStr
				.append("ENDDATETIME,CHECKITEM,CHECKNAME,CHECKTYPE,CHECKLEVEL,CHECKAGENTCODE,CHECKAGENTID,REMARK)");
		sqlStr
				.append(" values (:CHECKONMAINID,:CREATEUSERID,:CREATEDATE,:UPDATEUSERID,:UPDATEDATE,");
		sqlStr
				.append(":STARTDATETIME,:ENDDATETIME,:CHECKITEM,:CHECKNAME,:CHECKTYPE,:CHECKLEVEL,:CHECKAGENTCODE,:CHECKAGENTID,:REMARK)");
		Map<String, Object> paramMap = new HashMap<String, Object>();

		String createDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());
		String updateDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());

		String checkstartTimeString = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss").format(mode.getStartDateTime());
		String checkendTimeString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(mode.getEndDateTime());

		paramMap.put("CHECKONMAINID", CommonUtil.jugeAndFixNull(mode
				.getCheckOnMainID()));
		paramMap.put("CREATEUSERID", CommonUtil.jugeAndFixNull(mode
				.getCreateUid()));
		paramMap.put("CREATEDATE", DateUtil.timeToString(createDateString));
		paramMap.put("UPDATEUSERID", CommonUtil.jugeAndFixNull(mode
				.getUpdateId()));
		paramMap.put("UPDATEDATE", DateUtil.timeToString(updateDateString));
		paramMap.put("STARTDATETIME", DateUtil
				.timeToString(checkstartTimeString));
		paramMap.put("ENDDATETIME", DateUtil.timeToString(checkendTimeString));
		paramMap.put("CHECKITEM", CommonUtil
				.jugeAndFixNull(mode.getCheckItem()));
		paramMap.put("CHECKNAME", CommonUtil
				.jugeAndFixNull(mode.getCheckName()));
		paramMap.put("CHECKTYPE", CommonUtil
				.jugeAndFixNull(mode.getCheckType()));
		paramMap.put("CHECKLEVEL", CommonUtil.jugeAndFixNull(mode
				.getCheckLevel()));
		paramMap.put("CHECKAGENTCODE", CommonUtil.jugeAndFixNull(mode
				.getCheckAgentCode()));
		paramMap.put("CHECKAGENTID", CommonUtil.jugeAndFixNull(mode
				.getCheckAgentID()));
		paramMap.put("REMARK", CommonUtil.jugeAndFixNull(mode.getReMark()));
		return jdbcTemplate.update(sqlStr.toString(), paramMap) > 0;
	}
	/*
	 * 插入从表
	 */
	public boolean insertCheckOnItem(SaleCheckOnItem mode) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("insert into SALECHECKONITEM");
		sqlStr
				.append("(SALECHECKONITEMID,CREATEUSERID,CREATEDATE,UPDATEUSERID,UPDATEDATE,SALECHECKONMAINID,");
		sqlStr
				.append("CHECKRES,REMARK,CHECKEDNAME,CHECKEDAGENTCODE,CHECKEDAGENTID,AGENGLEVEL)");
		sqlStr
				.append("values (:CHECKONITEMID,:CREATEUSERID,:CREATEDATE,:UPDATEUSERID,:UPDATEDATE,");
		sqlStr
				.append(":CHECKONMAINID,:CHECKRES,:REMARK,:CHECKEDNAME,:CHECKEDAGENTCODE,:CHECKEDAGENTID,:AGENGLEVEL)");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String createDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());
		String updateDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());

		paramMap.put("CHECKONITEMID", CommonUtil.jugeAndFixNull(mode
				.getCheckOnItemID()));
		paramMap.put("CREATEUSERID", CommonUtil.jugeAndFixNull(mode
				.getCreateUid()));
		paramMap.put("CREATEDATE", DateUtil.timeToString(createDateString));
		paramMap.put("UPDATEUSERID", CommonUtil.jugeAndFixNull(mode
				.getUpdateId()));
		paramMap.put("UPDATEDATE", DateUtil.timeToString(updateDateString));
		paramMap.put("CHECKONMAINID", CommonUtil.jugeAndFixNull(mode
				.getCheckOnMainID()));
		paramMap.put("CHECKRES", CommonUtil.jugeAndFixNull(mode.getCheckRes()));
		paramMap.put("REMARK", CommonUtil.jugeAndFixNull(mode.getRemark()));
		paramMap.put("CHECKEDNAME", CommonUtil.jugeAndFixNull(mode
				.getCheckedName()));
		paramMap.put("CHECKEDAGENTCODE", CommonUtil.jugeAndFixNull(mode
				.getCheckedAgentCode()));
		paramMap.put("CHECKEDAGENTID", CommonUtil.jugeAndFixNull(mode
				.getCheckedAgentID()));
		paramMap.put("AGENGLEVEL", CommonUtil.jugeAndFixNull(mode
				.getAgengLevel()));
		return jdbcTemplate.update(sqlStr.toString(), paramMap) > 0;
	}

	/*
	 * 当前插入考勤统计
	 */
	public List<SaleStatisticsAttendance> thisinto(String mainid) {
		List<SaleStatisticsAttendance> list = new ArrayList<SaleStatisticsAttendance>();
		String sqlStr = "select checkitem,agentLevel,checkStartTime,checkEndTime,ontime,belate,leave,noCheckWork,attendance" +
				"  from table(pkg_sfa_AttendanceService.thisinto(:MAINID))";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("MAINID", CommonUtil.jugeAndFixNull(mainid));
		list = jdbcTemplate.query(sqlStr, paramMap,
				new BeanPropertyRowMapper<SaleStatisticsAttendance>(
						SaleStatisticsAttendance.class));
		return list;
	}

	/*
	 * 当前插入考勤明细
	 */
	public List<SaleStatisticsAttendance> attendancedetail(String mainID,
			String checkStatus, String agentLevel) {
		List<SaleStatisticsAttendance> list = new ArrayList<SaleStatisticsAttendance>();
		String sqlStr = "select agentCode,agentName,agentLevel,checkStartTime,checkEndTime,checkStatus" +
				" from table(pkg_sfa_AttendanceService.attendancedetail(:MAINID,:CHECKSTATUS,:AGENTLEVEL))";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("MAINID", CommonUtil.jugeAndFixNull(mainID));
		paramMap.put("CHECKSTATUS", CommonUtil.jugeAndFixNull(checkStatus));
		paramMap.put("AGENTLEVEL", CommonUtil.jugeAndFixNull(agentLevel));
		list = jdbcTemplate.query(sqlStr, paramMap,
				new BeanPropertyRowMapper<SaleStatisticsAttendance>(
						SaleStatisticsAttendance.class));
		return list;
	}

	/*
	 * 考勤统计(个人)
	 */
	public Pagination statisticsAttendance(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String begindate, String enddate, String checkitem,
			String agentlevel, String agents) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		if (!CommonUtil.isEmpty(agentlevel) && !"ALL".equals(agentlevel)) {
			agentlevel = agentlevel.substring(15);
		} else {
			agentlevel = "ALL";
		}
		String sqlStr = "select agentcode,agentname,agentlevel,ontime,belate,leave,nowork,attendance from table" +
				"(pkg_sfa_AttendanceService.statisticsAttendance(:BEGINDATE,:ENDDATE,:CHECKITEM,:AGENTLEVEL,:AGENTS))";
		paramMap.put("BEGINDATE", begindate);
		paramMap.put("ENDDATE", enddate);
		paramMap.put("CHECKITEM", CommonUtil.jugeAndFixNull(checkitem));
		paramMap.put("AGENTLEVEL", CommonUtil.jugeAndFixNull(agentlevel));
		paramMap.put("AGENTS", CommonUtil.jugeAndFixNull(agents));
		
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	 /*
	 * 考勤统计(总体)
	 */
	 public Pagination statisticsAttendanceAll(
			 int currentPage,//页码
			 int numPerPage,//页尺寸
			 String url,//分页url,
			 String begintime,String endtime,String checkitem,String agentlevel){
		 if(!"ALL".equals(agentlevel) && !CommonUtil.isEmpty(agentlevel) ){
			 agentlevel = agentlevel.substring(15);	
			}else{
				agentlevel = "ALL";
			}
		 Map<String, Object> paramMap = new HashMap<String, Object>();
		 
		String sqlStr = "select checkitem,agentlevel,checkstarttime,checkendtime,ontime,belate,leave,nowork,attendance from table" +
				"(pkg_sfa_AttendanceService.statisticsAttendanceAll(:BEGINTIME,:ENDTIME,:CHECKITEM,:AGENTLEVEL))";
		paramMap.put("BEGINTIME", begintime);
		paramMap.put("ENDTIME", endtime);
		paramMap.put("CHECKITEM", CommonUtil.jugeAndFixNull(checkitem));
		paramMap.put("AGENTLEVEL", CommonUtil.jugeAndFixNull(agentlevel));
	
		 return new Pagination(sqlStr,paramMap,currentPage,numPerPage,url,jdbcTemplate);
	 }

	 /*
	 * 考勤统计(总体)明细
	 */
	 public Pagination allDetail(
			 int currentPage,//页码
			 int numPerPage,//页尺寸
			 String url,//分页url
			 String checkstatus,
			 String checkitem,
			 String agentlevel,
			 String starttime,
			 String endtime){
		 
		 Map <String,Object> paramMap = new HashMap<String, Object>();
		 StringBuilder sqlStr = new StringBuilder();
		 sqlStr.append( " select * from (select it.checkedagentcode as agentCode,it.checkedname as agentName,it.agenglevel as agentLevel,");
		 sqlStr.append( " count(1) as noCheckWork from salecheckonmain ma ,salecheckonitem it where ma.salecheckonmainid=it.salecheckonmainid");
		 sqlStr.append( " and it.checkres=:CHECKSTATUS and ma.checkitem=:CHECKITEM and it.agenglevel=:AGENTLEVEL");
		 paramMap.put("BEGINTIME", starttime);
		 paramMap.put("ENDTIME", endtime);
		 sqlStr.append( " and startdatetime=TO_date(:BEGINTIME,'yyyy-mm-dd hh24:mi:ss') and enddatetime=TO_date(:ENDTIME,'yyyy-mm-dd hh24:mi:ss')"); 
		 sqlStr.append( " group by it.checkedagentcode,it.checkedname,it.agenglevel)");
		 
		 paramMap.put("CHECKSTATUS", CommonUtil.jugeAndFixNull(checkstatus));
		 paramMap.put("CHECKITEM", CommonUtil.jugeAndFixNull(checkitem));
		 paramMap.put("AGENTLEVEL", CommonUtil.jugeAndFixNull(agentlevel));
		 paramMap.put("BEGINTIME", starttime);
		 paramMap.put("ENDTIME", endtime);
		 
		 return new Pagination(sqlStr.toString(),paramMap,currentPage,numPerPage,url,jdbcTemplate); 
	 }

	/*
	 * 考勤个人明细
	 */
	public List<SaleCheckWorkOn> personalDetail(String agentcode,
			String checkstatus, String checkitem) {
		List<SaleCheckWorkOn> list = new ArrayList<SaleCheckWorkOn>();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr
				.append(" select row_number() over(order by it.checkres desc ) seq,it.checkres as checkstatus,ma.startdatetime as checkStartTime,");
		sqlStr
				.append(" ma.enddatetime as checkEndTime,ma.checkname as createUser,ma.checkitem,it.remark as reamk from");
		sqlStr
				.append(" SALECHECKONMAIN ma,SALECHECKONITEM it where ma.salecheckonmainid=it.salecheckonmainid");
		sqlStr
				.append(" and it.checkres=:CHECKSTATUS and it.checkedagentcode=:AGENTCODE");
		paramMap.put("AGENTCODE", CommonUtil.jugeAndFixNull(agentcode));
		paramMap.put("CHECKSTATUS", CommonUtil.jugeAndFixNull(checkstatus));

		if (!CommonUtil.isEmpty(checkitem) && !checkitem.equals("ALL")) {
			sqlStr.append(" and ma.checkitem=:CHECKITEM ");
			paramMap.put("CHECKITEM", CommonUtil.jugeAndFixNull(checkitem));
		}
		list = jdbcTemplate.query(sqlStr.toString(), paramMap,
				new BeanPropertyRowMapper<SaleCheckWorkOn>(
						SaleCheckWorkOn.class));
		return list;
	}

	/*
	 * 根据编号获取本人等级
	 */
	public String agentlevel(String agentcode) {
		String level = null;
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select AGENTLEVELCODE from agentbroker where agentcode=:AGENTCODE";
		paramMap.put("AGENTCODE", agentcode);
		level = jdbcTemplate.queryForObject(sqlStr, paramMap,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("AGENTLEVELCODE");
					}
				});
		return level;
	}

	/*
	 * 根据ID获取本人等级
	 */
	public String agentlevelID(String agentId) {
		String level = null;
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select AGENTLEVELCODE from agentbroker where agentId=:AGENTID";
		paramMap.put("AGENTID", agentId);
		level = jdbcTemplate.queryForObject(sqlStr, paramMap,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("AGENTLEVELCODE");
					}
				});
		return level;
	}

	/*
	 * 根据ID获取本人编号
	 */
	public String agentIDCode(String agentId) {
		String agentcode = null;
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select AGENTCODE from agentbroker where agentId=:AGENTID";
		paramMap.put("AGENTID", agentId);
		agentcode = jdbcTemplate.queryForObject(sqlStr, paramMap,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("AGENTCODE");
					}
				});
		return agentcode;
	}

	/*
	 * 根据ID获取本人编号
	 */
	public String agentCodeID(String agentCode) {
		String agentID = null;
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select agentId from agentbroker where agentcode=:AGENTCODE";
		paramMap.put("AGENTCODE", agentCode);
		agentID = jdbcTemplate.queryForObject(sqlStr, paramMap,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("AGENTID");
					}
				});
		return agentID;
	}

	/*
	 * 根据ID获取本人相关所有信息
	 */
	public TeamAgentInfoEntity getAgentInfo(String AGENTID) {
		String sqlStr = " select * from TABLE(pkg_sfa_Common.getAgentInfoById(:I_AGENTID)) ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("I_AGENTID", CommonUtil.jugeAndFixNull(AGENTID));
		TeamAgentInfoEntity agentinfo = null;
		agentinfo = jdbcTemplate.queryForObject(sqlStr, paramMap,
				new BeanPropertyRowMapper<TeamAgentInfoEntity>(
						TeamAgentInfoEntity.class));
		return agentinfo;
	}
}
